﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using TecBaseObjects.Models;
using TecBaseObjects.Objects;
using TecData.Manager;
using TecExtensions;

namespace TecData.Layer {

    internal class CashSaleDataLayer : BaseSqlDataLayer {

        #region Members

        private const string loadCashSaleStatement = @"CashSale.GetCashSale";
        private const string loadCashSaleDetailsStatement = @"CashSale.GetCashSaleDetails";
        private const string loadCashSaleJobDetailsStatement = @"Job.GetJobs";
        private const string saveCashSaleStatement = @"CashSale.SaveCashSale";
        private const string LoadCashSaleGetInvoiceNumber = @"CashSale.GetInvoiceNumber";
        private const string loadCashSaleProduct = @"Products.GetProducts";
        private const string loadCashSalePaymentTypes = @"dbo.GetPaymentTypes";

        #endregion

        #region Load

        public virtual int GetCashSaleInvoiceNumber(int cashSaleID, int userID) {
            int ret = 0;
            using (IDataReader reader = this.GetDataReaderFromStoredProcedure(LoadCashSaleGetInvoiceNumber, new SqlParameter("@CashSaleID", cashSaleID), new SqlParameter("@UserID", userID))) {
                ret = (reader.Read() ? this.GetInt32(reader, "InvoiceNo") : 0);
            }
            return ret;
        }

        public virtual CashSaleModel LoadCashSale(int cashSaleID) {
            string sql = loadCashSaleStatement;
            CashSaleModel ret;
            using (IDataReader reader = this.GetDataReaderFromStoredProcedure(sql, new SqlParameter("@inCashSaleID", cashSaleID))) {
                ret = (reader.Read() ? LoadCashSaleFromDataReader(reader) : null);
            }
            if (ret != null) {
                FillMyDetails(ret);
                FillMyJobDetails(ret);
            }
            return ret;
        }

        public virtual List<CashSaleModel> LoadCashSales() {
            string sql = loadCashSaleStatement;
            List<CashSaleModel> retList = new List<CashSaleModel>();
            using (DataTable dt = this.GetDataTableFromStoredProcedure(sql) ){
                foreach (DataRow dr in dt.Rows) {
                    CashSaleModel sm = new CashSaleModel();
                    sm.CashSaleID = (int)dr["CashSaleID"];
                    sm.InvoiceNo = (int)dr["InvoiceNo"];
                    if (dr["GrossPrice"].ToString() != "") {
                        sm.GrossPrice = Convert.ToDouble(dr["GrossPrice"]);
                    }
                    sm.LoginName = (string)dr["LoginName"].ToString();
                    if (dr["OrgKey"].ToString() != "") {
                        sm.OrgKey = (int)dr["OrgKey"];
                    }
                    if (dr["OrgName"].ToString() != "") {
                        sm.OrgName = (string)dr["OrgName"];
                    }
                    if (dr["OrgFullName"].ToString() != "") {
                        sm.OrgFullName = (string)dr["OrgFullName"];
                    }
                    if (Convert.ToString(dr["SaleDate"]) != "") {
                        sm.SaleDate = (DateTime)dr["SaleDate"];
                    }
                    if (dr["SalePrice"].ToString() != "") {
                        sm.SalePrice = Convert.ToDouble(dr["SalePrice"]);
                    }
                    if (dr["Tax"].ToString() != "") {
                        sm.Taxes = Convert.ToDouble(dr["Tax"]);
                    }
                    sm.UserID = Convert.ToInt32(dr["UserID"]);
                    sm.PaymentMode = Convert.ToInt32(dr["PaymentMode"]);
                    if (Convert.ToString(dr["OrderDate"]) != "") {
                        sm.OrderDate = Convert.ToDateTime(dr["OrderDate"]);
                    }
                    sm.OrderDetail = (string)dr["OrderDetail"].ToString();
                    sm.DeliverName = (string)dr["DeliverName"].ToString();
                    sm.DeliverName2 = (string)dr["DeliverName2"].ToString();
                    sm.DeliverOrt = (string)dr["DeliverOrt"].ToString();
                    sm.DeliverPlz = (string)dr["DeliverPlz"].ToString();
                    sm.DeliverStrasse = (string)dr["DeliverStrasse"].ToString();
                    sm.UseDeliveryAddress = Convert.ToBoolean(dr["UseDeliveryAddress"]);
                    if (Convert.ToString(dr["WriteToLiquiDate"]) != "") {
                        sm.WriteToLiquiDate = Convert.ToDateTime(dr["WriteToLiquiDate"]);
                    }
                    if (Convert.ToString(dr["WriteInvoiceToBvDate"]) != "") {
                        sm.WriteInvoiceToBvDate = Convert.ToDateTime(dr["WriteInvoiceToBvDate"]);
                    }
                    if (Convert.ToString(dr["WriteInvoiceToVrspDate"]) != "") {
                        sm.WriteInvoiceToVrspDate = Convert.ToDateTime(dr["WriteInvoiceToVrspDate"]);
                    }
                    retList.Add(sm);
                }
            }
            return retList;
        }

        public virtual PaymentModeObj LoadPaymentType(int paymentID) {
            string sql = loadCashSalePaymentTypes;
            PaymentModeObj pay = new PaymentModeObj();
            using (DataTable dt = this.GetDataTableFromStoredProcedure(sql, new SqlParameter("@PaymentID", paymentID))) {
                if(dt.Rows.Count > 0) {
                    DataRow dr = dt.Rows[0];
                    pay.PaymentMode = Convert.ToInt32(dr["PaymentID"]);
                    pay.PaymentModeText = Convert.ToString(dr["PaymentText"]);
                }
            }
            return pay;
        }

        public virtual List<PaymentModeObj> LoadPaymentTypes() {
            string sql = loadCashSalePaymentTypes;
            List<PaymentModeObj> retList = new List<PaymentModeObj>();
            using (DataTable dt = this.GetDataTableFromStoredProcedure(sql)) {
                foreach (DataRow dr in dt.Rows) {
                    PaymentModeObj pt = new PaymentModeObj();
                    pt.PaymentMode = Convert.ToInt32(dr["PaymentID"]);
                    pt.PaymentModeText = Convert.ToString(dr["PaymentText"]);
                    retList.Add(pt);
                }
            }
            return retList;
        }

        public virtual CashSaleProductsModel LoadProduct(int productID) {
            CashSaleProductsModel ret = new CashSaleProductsModel();
            using (IDataReader reader = this.GetDataReaderFromStoredProcedure(loadCashSaleProduct, new SqlParameter("@ProductID", productID))) {
                if (reader.Read()) {
                    ret.Categorie = this.GetString(reader, "Categorie");
                    ret.ContributionMargin = (double)this.GetDecimal(reader, "ContributionMargin");
                    ret.CostPrice = (double)this.GetDecimal(reader, "CostPrice");
                    ret.ItemNumber = this.GetString(reader, "ItemNumber");
                    ret.ListPrice = (double)this.GetDecimal(reader, "ListPrice");
                    ret.Manufacturer = this.GetString(reader, "Manufacturer");
                    ret.ManufacturerOrgKey = this.GetInt32(reader, "ManufacturerOrgKey");
                    ret.ProductID = this.GetInt32(reader, "ProductID");
                    ret.ProductName = this.GetString(reader, "ProductName");
                    ret.Provider = this.GetString(reader, "Provider");
                    ret.ProviderOrgKey = this.GetInt32(reader, "ProviderOrgKey");
                    ret.Rabatt1 = (double)this.GetDecimal(reader, "Rabatt1");
                    ret.Rabatt2 = (double)this.GetDecimal(reader, "Rabatt2");
                    ret.SalesPrice = (double)this.GetDecimal(reader, "SalesPrice");
                    ret.Skonto = (double)this.GetDecimal(reader, "Skonto");
                    ret.SubCategorie = this.GetString(reader, "SubCategorie");
                    ret.SurCharge = (double)this.GetDecimal(reader, "SurCharge");
                    ret.TaxCode = this.GetByte(reader, "TaxCode");
                    ret.Unity = this.GetString(reader, "Unity");
                    ret.Hour = (double)this.GetDecimal(reader, "Hour");
                    ret.Effort = (double)this.GetDecimal(reader, "Effort");
                    ret.CategorieShort = this.GetString(reader, "CategorieShort");
                    ret.SubCategorieShort = this.GetString(reader, "SubCategorieShort");
                }
            }
            return ret;
        }

        #endregion

        #region Save

        public virtual bool SaveCashSale(CashSaleModel model) {
            int ret = 0;
            SqlParameter[] parameters = GetParameters(model);
            if (model.CashSaleID != 0) {
                ret = ExecuteStoredProcedure(saveCashSaleStatement, parameters);
                if (ret != -1) {
                    return false;
                }
                model.IsDirty = false;
                return true;
            }
            using (IDataReader reader = this.GetDataReaderFromStoredProcedure(saveCashSaleStatement, parameters)) {
                if (reader.Read()) {
                    if (this.GetInt32(reader, "CashSaleID") != 0) {
                        model.CashSaleID = this.GetInt32(reader, "CashSaleID");
                    }
                    if (this.GetInt32(reader, "Return") != 0) {
                        return false;
                    }
                }
            }
            model.IsDirty = false;
            return true;
        }

        #endregion

        #region Methods

        private void FillMyDetails(CashSaleModel myCashSale) {
            string sql = loadCashSaleDetailsStatement;
            using (IDataReader reader = this.GetDataReaderFromStoredProcedure(sql, new SqlParameter("@inCashSaleID", myCashSale.CashSaleID))) {
                while (reader.Read()) {
                    CashSaleDetailsModel item = LoadCashSaleDetailsFromDataReader(reader);
                    myCashSale.AddMyDetail(item);
                }
            }
        }

        private void FillMyJobDetails(CashSaleModel myCashSale) {
            myCashSale.MyJobDetails = CommonDataManager.LoadJobs(myCashSale.CashSaleID, false);
        }

        #endregion

        #region Functions

        protected virtual SqlParameter[] GetParameters(CashSaleModel model) {
            DataTable dt = GetDetailsDataTable();

            foreach (CashSaleDetailsModel csdm in model.MyDetails) {
                if (csdm.ProductID != 0) {
                    DataRow dr = dt.NewRow();
                    dr["DetailsID"] = csdm.DetailsID;
                    dr["CashSaleID"] = csdm.CashSaleID;
                    dr["ProductID"] = csdm.ProductID;
                    dr["Quantity"] = csdm.Quantity;
                    dr["Price"] = csdm.Price;
                    dr["RabattPercent1"] = csdm.RabattPercent1;
                    dr["RabattPercent2"] = csdm.RabattPercent2;
                    dr["RabattFix"] = csdm.RabattFix;
                    dr["TaxCode"] = csdm.TaxCode;
                    dr["TaxRate"] = csdm.TaxRate;
                    dt.Rows.Add(dr);
                }
            }

            DataTable dtj = GetJobDetailsDataTable();

            foreach (JobObj job in model.MyJobDetails) {
                DataRow dr = dtj.NewRow();
                dr["JobID"] = job.JobID;
                dr["UserID"] = job.UserID;
                dr["JobType"] = job.JobType;
                dr["Title"] = job.Title;
                dr["Einheit"] = job.Einheit;
                dr["Fahrzeug"] = job.Fahrzeug;
                dr["Datum"] = job.Datum;
                dr["StartTime"] = job.StartTime;
                dr["EndTime"] = job.EndTime;
                dr["Leistung"] = job.Leistung;
                dr["LeistungProzent"] = job.LeistungProzent;
                dr["LeistungZeit"] = job.LeistungZeit;
                dr["ProjektName"] = job.ProjectName;
                dr["Status"] = job.Status;
                dr["CashSaleID"] = job.CashSaleID;
                dr["ProjectID"] = job.ProjectID;
                dr["Area"] = job.Area;
                dr["SubArea"] = job.SubArea;
                dr["Sb"] = job.Sb;
                dr["Ss"] = job.Ss;
                dr["Datei"] = job.File;
                dr["Fa"] = job.Fa;
                dr["Vr"] = job.Vr;
                dtj.Rows.Add(dr);
            }

            SqlParameter[] ps = new SqlParameter[17];

            ps[0] = new SqlParameter("@CashSaleID", model.CashSaleID);
            ps[1] = new SqlParameter("@InvoiceNo", model.InvoiceNo);
            ps[2] = new SqlParameter("@SaleDate", model.SaleDate);
            ps[3] = new SqlParameter("@OrgKey", model.OrgKey);
            ps[4] = new SqlParameter("@UserID", model.UserID);
            ps[5] = new SqlParameter("@PaymentMode", model.PaymentMode);
            ps[6] = new SqlParameter("@OrderDate", model.OrderDate);
            ps[7] = new SqlParameter("@OrderDetail", model.OrderDetail);
            ps[8] = new SqlParameter("@Details", dt);
            ps[9] = new SqlParameter("@DeliverName", model.DeliverName);
            ps[10] = new SqlParameter("@DeliverName2", model.DeliverName2);
            ps[11] = new SqlParameter("@DeliverOrt", model.DeliverOrt);
            ps[12] = new SqlParameter("@DeliverPlz", model.DeliverPlz);
            ps[13] = new SqlParameter("@DeliverStrasse", model.DeliverStrasse);
            ps[14] = new SqlParameter("@UseDeliveryAddress", model.UseDeliveryAddress);
            ps[15] = new SqlParameter("@JobDetails", dtj);
            if (model.WriteToLiquiDate.Year > 2000) {
                ps[16] = new SqlParameter("@WriteToLiquiDate", model.WriteToLiquiDate);
            }
            return ps;
        }
                
        private CashSaleModel LoadCashSaleFromDataReader(IDataReader reader) {
            CashSaleModel ret = new CashSaleModel();
            ret.CashSaleID = this.GetInt32(reader, "CashSaleID");
            ret.InvoiceNo = this.GetInt32(reader, "InvoiceNo");
            ret.OrgKey = this.GetInt32(reader, "OrgKey");
            ret.OrgName = this.GetString(reader, "OrgName");
            ret.OrgFullName = this.GetString(reader, "OrgFullName");
            ret.SaleDate = this.GetDateTime(reader, "SaleDate");
            ret.SalePrice = (double)this.GetDecimal(reader, "SalePrice");
            ret.Taxes = (double)this.GetDecimal(reader, "Tax");
            ret.GrossPrice = (double)this.GetDecimal(reader, "GrossPrice");
            ret.UserID = this.GetInt32(reader, "UserID");
            ret.PaymentMode = this.GetInt32(reader,"PaymentMode");
            ret.OrderDate = this.GetDateTime(reader, "OrderDate");
            ret.OrderDetail = this.GetString(reader, "OrderDetail");
            ret.DeliverName = this.GetString(reader, "DeliverName");
            ret.DeliverName2 = this.GetString(reader, "DeliverName2");
            ret.DeliverOrt = this.GetString(reader, "DeliverOrt");
            ret.DeliverPlz = this.GetString(reader, "DeliverPlz");
            ret.DeliverStrasse = this.GetString(reader, "DeliverStrasse");
            ret.UseDeliveryAddress = this.GetBoolean(reader, "UseDeliveryAddress");
            ret.WriteToLiquiDate = this.GetDateTime(reader, "WriteToLiquiDate");
            ret.WriteInvoiceToBvDate = this.GetDateTime(reader, "WriteInvoiceToBvDate");
            ret.WriteInvoiceToVrspDate = this.GetDateTime(reader, "WriteInvoiceToVrspDate");
            return ret;
        }

        private CashSaleDetailsModel LoadCashSaleDetailsFromDataReader(IDataReader reader) {
            CashSaleDetailsModel ret = new CashSaleDetailsModel();
            ret.CashSaleID = this.GetInt32(reader, "CashSaleID");
            ret.DetailsID = this.GetInt32(reader, "DetailsID");
            ret.Price = (double)this.GetDecimal(reader, "Price");
            ret.ProductID = this.GetInt32(reader, "ProductID");
            ret.ProductName = (string)this.GetString(reader, "ProductName");
            ret.Quantity = (double)this.GetDecimal(reader, "Quantity");
            ret.RabattFix = (double)this.GetDecimal(reader, "RabattFix");
            ret.RabattPercent1 = (double)this.GetDecimal(reader, "RabattPercent1");
            ret.RabattPercent2 = (double)this.GetDecimal(reader, "RabattPercent2");
            ret.TaxCode = this.GetByte(reader, "TaxCode");
            ret.TaxRate = (double)this.GetDecimal(reader, "TaxRate");
            ret.Unity = (string)this.GetString(reader, "Unity");
            return ret;
        }

        private DataTable GetDetailsDataTable() {
            DataTable dt = new DataTable();
            DataColumn dc = new DataColumn("DetailsID");
            dc.DataType = Type.GetType("System.Int32");
            dt.Columns.Add(dc);

            dc = new DataColumn("CashSaleID");
            dc.DataType = Type.GetType("System.Int32");
            dt.Columns.Add(dc);

            dc = new DataColumn("ProductID");
            dc.DataType = Type.GetType("System.Int32");
            dt.Columns.Add(dc);

            dc = new DataColumn("Quantity");
            dc.DataType = Type.GetType("System.Double");
            dt.Columns.Add(dc);

            dc = new DataColumn("Price");
            dc.DataType = Type.GetType("System.Double");
            dt.Columns.Add(dc);

            dc = new DataColumn("RabattPercent1");
            dc.DataType = Type.GetType("System.Int32");
            dt.Columns.Add(dc);

            dc = new DataColumn("RabattPercent2");
            dc.DataType = Type.GetType("System.Double");
            dt.Columns.Add(dc);

            dc = new DataColumn("RabattFix");
            dc.DataType = Type.GetType("System.Double");
            dt.Columns.Add(dc);

            dc = new DataColumn("TaxCode");
            dc.DataType = Type.GetType("System.Int32");
            dt.Columns.Add(dc);

            dc = new DataColumn("TaxRate");
            dc.DataType = Type.GetType("System.Double");
            dt.Columns.Add(dc);

            return dt;
        }

        private DataTable GetJobDetailsDataTable() {
            DataTable dtj = new DataTable();
            DataColumn dcj = new DataColumn("JobID");
            dcj.DataType = Type.GetType("System.Guid");
            dtj.Columns.Add(dcj);

            dcj = new DataColumn("UserID");
            dcj.DataType = Type.GetType("System.Int32");
            dtj.Columns.Add(dcj);

            dcj = new DataColumn("JobType");
            dcj.DataType = Type.GetType("System.Int32");
            dtj.Columns.Add(dcj);

            dcj = new DataColumn("Title");
            dcj.DataType = Type.GetType("System.String");
            dtj.Columns.Add(dcj);

            dcj = new DataColumn("Einheit");
            dcj.DataType = Type.GetType("System.String");
            dtj.Columns.Add(dcj);

            dcj = new DataColumn("Fahrzeug");
            dcj.DataType = Type.GetType("System.String");
            dtj.Columns.Add(dcj);

            dcj = new DataColumn("Datum");
            dcj.DataType = Type.GetType("System.DateTime");
            dtj.Columns.Add(dcj);

            dcj = new DataColumn("StartTime");
            dcj.DataType = Type.GetType("System.TimeSpan");
            dtj.Columns.Add(dcj);

            dcj = new DataColumn("EndTime");
            dcj.DataType = Type.GetType("System.TimeSpan");
            dtj.Columns.Add(dcj);

            dcj = new DataColumn("Leistung");
            dcj.DataType = Type.GetType("System.String");
            dtj.Columns.Add(dcj);

            dcj = new DataColumn("LeistungProzent");
            dcj.DataType = Type.GetType("System.String");
            dtj.Columns.Add(dcj);

            dcj = new DataColumn("LeistungZeit");
            dcj.DataType = Type.GetType("System.Double");
            dtj.Columns.Add(dcj);

            dcj = new DataColumn("ProjektName");
            dcj.DataType = Type.GetType("System.String");
            dtj.Columns.Add(dcj);

            dcj = new DataColumn("Status");
            dcj.DataType = Type.GetType("System.Int16");
            dtj.Columns.Add(dcj);

            dcj = new DataColumn("CashSaleID");
            dcj.DataType = Type.GetType("System.Int32");
            dtj.Columns.Add(dcj);
            
            dcj = new DataColumn("ProjectID");
            dcj.DataType = Type.GetType("System.Int32");
            dtj.Columns.Add(dcj);

            dcj = new DataColumn("Area");
            dcj.DataType = Type.GetType("System.String");
            dtj.Columns.Add(dcj);

            dcj = new DataColumn("SubArea");
            dcj.DataType = Type.GetType("System.String");
            dtj.Columns.Add(dcj);

            dcj = new DataColumn("Sb");
            dcj.DataType = Type.GetType("System.String");
            dtj.Columns.Add(dcj);

            dcj = new DataColumn("Ss");
            dcj.DataType = Type.GetType("System.String");
            dtj.Columns.Add(dcj);

            dcj = new DataColumn("Datei");
            dcj.DataType = Type.GetType("System.String");
            dtj.Columns.Add(dcj);

            dcj = new DataColumn("Fa");
            dcj.DataType = Type.GetType("System.String");
            dtj.Columns.Add(dcj);

            dcj = new DataColumn("Vr");
            dcj.DataType = Type.GetType("System.String");
            dtj.Columns.Add(dcj);

            return dtj;
        }
                
        #endregion

    }
}
